﻿using System;
using System.Text;
using System.Data;
using System.Linq;
using System.Data.Common;
using System.Collections.Generic;
using System.Collections.Specialized;

using Job.Core;
using Job.Core.Data;
using Job.Core.Module;
using Job.Core.Collections;

namespace Job.ManageMember
{
    /// <summary>
    /// Member模块Sql实现类。
    /// </summary>
    public class SqlMemberRepository : DbRepositoryProvider, IMemberRepository
    {

        #region Role

        private DichotomyList<Role> roleCache;
        protected DichotomyList<Role> RoleCache
        {
            get { return roleCache; }
        }

        /// <summary>
        /// 获得Role实例。
        /// </summary>
        protected virtual Role GetRole(DataReader dr)
        {
            return new Role
            {
                KeyId = dr.GetValue<Int32>("RoleID"),
                RoleName = dr.GetValue<String>("RoleName"),
                IsSys = dr.GetValue<Boolean>("IsSys"),
                //SysType = dr.GetValue<SysTypeStatus>("SysType"),
                Description = dr.GetValue<String>("Description")
            };
        }

        /// <summary>
        /// 添加Role。
        /// </summary>
        public virtual bool CreateRole(Role role)
        {
            DbConnection conn = DbHelper.CreateConnection();
            DbCommand cmd = DbHelper.CreateCommand();
            cmd.Connection = conn;
            cmd.CommandText = string.Format("INSERT INTO {0}Role(RoleName,IsSys,Description) VALUES(@RoleName,@IsSys,@Description);SELECT SCOPE_IdENTITY();", TablePrefix);
            cmd.Parameters.Add(DbHelper.NewParam("@RoleName", DbType.String, 15, role.RoleName));
            cmd.Parameters.Add(DbHelper.NewParam("@IsSys", DbType.Boolean, role.IsSys));
            cmd.Parameters.Add(DbHelper.NewParam("@Description", DbType.String, 255, role.Description));

            role.KeyId = Convert.ToInt32(cmd.ExecuteScalar());
            cmd.Parameters.Clear();

            bool result = role.KeyId != default(Int32);
            if (result) RoleCache.Add(role);
            return result;
        }

        /// <summary>
        /// 更新Role。
        /// </summary>
        public virtual bool UpdateRole(Role role)
        {
            DbConnection conn = DbHelper.CreateConnection();
            DbCommand cmd = DbHelper.CreateCommand();
            cmd.Connection = conn;
            cmd.CommandText = string.Format("UPDATE {0}Role SET RoleName=@RoleName,IsSys=@IsSys,Description=@Description WHERE RoleID=@RoleID", TablePrefix);
            cmd.Parameters.Add(DbHelper.NewParam("@RoleName", DbType.String, 15, role.RoleName));
            cmd.Parameters.Add(DbHelper.NewParam("@IsSys", DbType.Boolean, role.IsSys));
            cmd.Parameters.Add(DbHelper.NewParam("@Description", DbType.String, 255, role.Description));
            cmd.Parameters.Add(DbHelper.NewParam("@RoleID", DbType.Int32, role.KeyId));

            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();

            bool result = val > 0;
            if (result) RoleCache.Toggle(role);
            return result;
        }

        /// <summary>
        /// 删除Role。
        /// </summary>
        public virtual bool DeleteRole(Role role)
        {
            DbConnection conn = DbHelper.CreateConnection();
            DbCommand cmd = DbHelper.CreateCommand();
            cmd.Connection = conn;
            cmd.CommandText = string.Format("DELETE FROM {0}Role WHERE RoleID=@RoleID", TablePrefix);
            cmd.Parameters.Add(DbHelper.NewParam("@RoleID", DbType.Int32, role.KeyId));

            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();

            bool result = val > 0;
            if (result) RoleCache.Remove(role.KeyId);
            return result;
        }

        /// <summary>
        /// 获取Role实例。
        /// </summary>
        public virtual Role GetRole(Int32 id)
        {
            Role role = null;
            //using(DataReader dr = new DataReader(DbHelper.ExecuteReader(string.Format("SELECT * FROM {0}Role WHERE RoleID=@RoleID", TablePrefix),
            //        DbHelper.NewParam("@RoleID", DbType.Int32, id))))
            //{
            //    if (dr.Read())
            //    {
            //        role = GetRole(dr);
            //    }
            //}

            role = RoleCache.GetOrDefault(id);
            return role;
        }

        /// <summary>
        /// 获得Role列表。
        /// </summary>
        public virtual IEnumerable<Role> GetRoles()
        {
            return RoleCache;
        }

        /// <summary>
        /// 加载Role列表。
        /// </summary>
        protected virtual IEnumerable<Role> LoadRoles()
        {

            using (DataReader dr = new DataReader(DbHelper.ExecuteReader(string.Format("SELECT * FROM {0}Role", TablePrefix))))
            {
                while (dr.Read())
                {
                    yield return GetRole(dr);
                }
            }

        }

        #endregion

        #region User

        private DichotomyList<User> userCache;
        protected DichotomyList<User> UserCache
        {
            get { return userCache; }
        }

        /// <summary>
        /// 获得User实例。
        /// </summary>
        protected virtual User GetUser(DataReader dr)
        {
            return new User
            {
                KeyId = dr.GetValue<Int32>("UserID"),
                UserName = dr.GetValue<String>("UserName"),
                Password = dr.GetValue<String>("Password"),
                RoleID = dr.GetValue<Int32>("RoleID"),
                Status = dr.GetValue<UserStatus>("Status"),
                Email = dr.GetValue<String>("Email"),
                CreateTime = dr.GetValue<DateTime>("CreateTime"),
                LoginCount = dr.GetValue<Int32>("LoginCount"),
                LoginError = dr.GetValue<Int32>("LoginError"),
                LoginErrorTime = dr.GetValue<DateTime?>("LoginErrorTime"),
                LoginErrorIP = dr.GetValue<String>("LoginErrorIP"),
                CurrentLoginTime = dr.GetValue<DateTime?>("CurrentLoginTime"),
                CurrentLoginIP = dr.GetValue<String>("CurrentLoginIP"),
                LastLoginTime = dr.GetValue<DateTime?>("LastLoginTime"),
                LastLoginIP = dr.GetValue<String>("LastLoginIP")
            };
        }

        /// <summary>
        /// 添加User。
        /// </summary>
        public virtual bool CreateUser(User user)
        {
            DbConnection conn = DbHelper.CreateConnection();
            DbCommand cmd = DbHelper.CreateCommand();
            cmd.Connection = conn;
            cmd.CommandText = string.Format("INSERT INTO {0}User(UserName,Password,RoleID,Status,Email,CreateTime,LoginCount,LoginError,LoginErrorTime,LoginErrorIP,CurrentLoginTime,CurrentLoginIP,LastLoginTime,LastLoginIP) VALUES(@UserName,@Password,@RoleID,@Status,@Email,@CreateTime,@LoginCount,@LoginError,@LoginErrorTime,@LoginErrorIP,@CurrentLoginTime,@CurrentLoginIP,@LastLoginTime,@LastLoginIP);SELECT SCOPE_IdENTITY();", TablePrefix);
            cmd.Parameters.Add(DbHelper.NewParam("@UserName", DbType.String, 15, user.UserName));
            cmd.Parameters.Add(DbHelper.NewParam("@Password", DbType.String, 127, user.Password));
            cmd.Parameters.Add(DbHelper.NewParam("@RoleID", DbType.Int32, user.RoleID));
            cmd.Parameters.Add(DbHelper.NewParam("@Status", DbType.Int32, (int)user.Status));
            cmd.Parameters.Add(DbHelper.NewParam("@Email", DbType.String, 63, user.Email));
            cmd.Parameters.Add(DbHelper.NewParam("@CreateTime", DbType.Date, user.CreateTime));
            cmd.Parameters.Add(DbHelper.NewParam("@LoginCount", DbType.Int32, user.LoginCount));
            cmd.Parameters.Add(DbHelper.NewParam("@LoginError", DbType.Int32, user.LoginError));
            cmd.Parameters.Add(DbHelper.NewParam("@LoginErrorTime", DbType.Date, user.LoginErrorTime));
            cmd.Parameters.Add(DbHelper.NewParam("@LoginErrorIP", DbType.String, 15, user.LoginErrorIP));
            cmd.Parameters.Add(DbHelper.NewParam("@CurrentLoginTime", DbType.Date, user.CurrentLoginTime));
            cmd.Parameters.Add(DbHelper.NewParam("@CurrentLoginIP", DbType.String, 15, user.CurrentLoginIP));
            cmd.Parameters.Add(DbHelper.NewParam("@LastLoginTime", DbType.Date, user.LastLoginTime));
            cmd.Parameters.Add(DbHelper.NewParam("@LastLoginIP", DbType.String, 15, user.LastLoginIP));

            user.KeyId = Convert.ToInt32(cmd.ExecuteScalar());
            cmd.Parameters.Clear();

            bool result = user.KeyId != default(Int32);
            if (result) UserCache.Add(user);
            return result;
        }

        /// <summary>
        /// 更新User。
        /// </summary>
        public virtual bool UpdateUser(User user)
        {
            DbConnection conn = DbHelper.CreateConnection();
            DbCommand cmd = DbHelper.CreateCommand();
            cmd.Connection = conn;
            cmd.CommandText = string.Format("UPDATE {0}User SET UserName=@UserName,Password=@Password,RoleID=@RoleID,Status=@Status,Email=@Email,CreateTime=@CreateTime,LoginCount=@LoginCount,LoginError=@LoginError,LoginErrorTime=@LoginErrorTime,LoginErrorIP=@LoginErrorIP,CurrentLoginTime=@CurrentLoginTime,CurrentLoginIP=@CurrentLoginIP,LastLoginTime=@LastLoginTime,LastLoginIP=@LastLoginIP WHERE UserID=@UserID", TablePrefix);
            cmd.Parameters.Add(DbHelper.NewParam("@UserName", DbType.String, 15, user.UserName));
            cmd.Parameters.Add(DbHelper.NewParam("@Password", DbType.String, 127, user.Password));
            cmd.Parameters.Add(DbHelper.NewParam("@RoleID", DbType.Int32, user.RoleID));
            cmd.Parameters.Add(DbHelper.NewParam("@Status", DbType.Int32, (int)user.Status));
            cmd.Parameters.Add(DbHelper.NewParam("@Email", DbType.String, 63, user.Email));
            cmd.Parameters.Add(DbHelper.NewParam("@CreateTime", DbType.Date, user.CreateTime));
            cmd.Parameters.Add(DbHelper.NewParam("@LoginCount", DbType.Int32, user.LoginCount));
            cmd.Parameters.Add(DbHelper.NewParam("@LoginError", DbType.Int32, user.LoginError));
            cmd.Parameters.Add(DbHelper.NewParam("@LoginErrorTime", DbType.Date, user.LoginErrorTime));
            cmd.Parameters.Add(DbHelper.NewParam("@LoginErrorIP", DbType.String, 15, user.LoginErrorIP));
            cmd.Parameters.Add(DbHelper.NewParam("@CurrentLoginTime", DbType.Date, user.CurrentLoginTime));
            cmd.Parameters.Add(DbHelper.NewParam("@CurrentLoginIP", DbType.String, 15, user.CurrentLoginIP));
            cmd.Parameters.Add(DbHelper.NewParam("@LastLoginTime", DbType.Date, user.LastLoginTime));
            cmd.Parameters.Add(DbHelper.NewParam("@LastLoginIP", DbType.String, 15, user.LastLoginIP));
            cmd.Parameters.Add(DbHelper.NewParam("@UserID", DbType.Int32, user.KeyId));

            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();

            bool result = val > 0;
            if (result) UserCache.Toggle(user);
            return result;
        }

        /// <summary>
        /// 删除User。
        /// </summary>
        public virtual bool DeleteUser(User user)
        {
            DbConnection conn = DbHelper.CreateConnection();
            DbCommand cmd = DbHelper.CreateCommand();
            cmd.Connection = conn;
            cmd.CommandText = string.Format("DELETE FROM {0}User WHERE UserID=@UserID", TablePrefix);
            cmd.Parameters.Add(DbHelper.NewParam("@UserID", DbType.Int32, user.KeyId));

            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();

            bool result = val > 0;
            if (result) UserCache.Remove(user.KeyId);
            return result;
        }

        /// <summary>
        /// 获取User实例。
        /// </summary>
        public virtual User GetUser(Int32 id)
        {
            User user = null;
            //using(DataReader dr = new DataReader(DbHelper.ExecuteReader(string.Format("SELECT * FROM {0}User WHERE UserID=@UserID", TablePrefix),
            //        DbHelper.NewParam("@UserID", DbType.Int32, id))))
            //{
            //    if (dr.Read())
            //    {
            //        user = GetUser(dr);
            //    }
            //}

            user = UserCache.GetOrDefault(id);
            return user;
        }

        /// <summary>
        /// 根据用户名获取User实例。
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public virtual User GetUser(string name)
        {
            return UserCache.FirstOrDefault(u => u.UserName == name);
        }

        /// <summary>
        /// 获得User列表。
        /// </summary>
        public virtual IEnumerable<User> GetUsers()
        {
            return UserCache;
        }

        /// <summary>
        /// 加载User列表。
        /// </summary>
        protected virtual IEnumerable<User> LoadUsers()
        {
            using (DataReader dr = new DataReader(DbHelper.ExecuteReader(string.Format("SELECT * FROM {0}User", TablePrefix))))
            {
                while (dr.Read())
                {
                    yield return GetUser(dr);
                }
            }
        }

        /// <summary>
        /// 获取分页 User 列表。
        /// </summary>
        public virtual IEnumerable<User> GetUsers(int pageIndex, int pageSize, out int count)
        {
            count = UserCache.Count;
            return UserCache.Skip((pageIndex - 1) * pageSize).Take(pageSize);
        }

        #endregion

        #region Overrides

        public override void Initialize(string name, NameValueCollection config)
        {
            base.Initialize(name, config);

            roleCache = DichotomyList<Role>.Synchronized(new DichotomyList<Role>(LoadRoles().ToList()));
            userCache = DichotomyList<User>.Synchronized(new DichotomyList<User>(LoadUsers().ToList()));
        }

        #endregion

    }
}